﻿using Sdk.Converts;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sdk.Converts
{
    public class SqlServerConvert : ISQLConvert
    {
        DocConverterOptions opt = new DocConverterOptions();

        /*
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tab_Deviceinfo]') AND type in (N'U'))
DROP TABLE [dbo].[tab_Deviceinfo]
GO

CREATE TABLE [dbo].[Untitled] (
  [Id] int IDENTITY NOT NULL,
  [name] varchar(255) NOT NULL,
  [birth] datetime NOT NULL,
  PRIMARY KEY CLUSTERED ([Id], [name])
)
GO

CREATE NONCLUSTERED INDEX [idxName] ON [dbo].[Untitled] ([name])
GO

EXEC sp_addextendedproperty 'MS_Description', N'cccc','SCHEMA', N'dbo','TABLE', N'Untitled','COLUMN', N'Id'
GO
         */

        public string ToSQL(ReadDocResult readDocResult, DocConverterOptions options)
        {
            opt = options;
            List<string> sqlParagraphs = new List<string>();

            foreach (var table in readDocResult.Tables)
            {
                
                List<string> sqlFields = new List<string>();
                List<string> sqlIndexs = new List<string>();
                List<string> sqlPKeys = new List<string>();
                List<string> sqlComments = new List<string>();
                foreach (var field in table.StructFields)
                {
                    string id = field.IsAutoGen ? "IDENTITY" : "";
                    string notNull = field.IsNullable ? "" : "NOT NULL";
                    string sqlField = $"[{field.FieldName}] {field.FieldTypeDb} {notNull} {id}".Trim();
                    sqlFields.Add(sqlField);

                    if (field.IsPrimaryKey)
                    {
                        sqlPKeys.Add($"[{field.FieldName}]");
                    }
                    if (field.IsIndex)
                    {
                        sqlIndexs.Add(field.FieldName);
                    }
                    if (!string.IsNullOrWhiteSpace(field.FieldComment))
                    {
                        var sqlComment = $"EXEC sp_addextendedproperty 'MS_Description', N'{field.FieldComment}','SCHEMA', N'dbo','TABLE', N'{table.TableName}','COLUMN', N'{field.FieldName}'";
                        sqlComments.Add(sqlComment);
                    }
                }

                var sqlParagrap = $"-- {table.Comment} \n";

                // Drop语句
                if (opt.IsWithDrop)
                {
                    string sqlDrop = "";
                    sqlDrop += $"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{table.TableName}]') AND type in (N'U')) \n";
                    sqlDrop += $"DROP TABLE [dbo].[{table.TableName}]\n";
                    sqlDrop += "GO\n";

                    sqlParagrap += sqlDrop;
                }

                // 建表语句
                {
                    var sqlCreate = $"CREATE TABLE [dbo].[{table.TableName}] (\n";
                    if (sqlFields.Count > 0)
                    {
                        sqlCreate += string.Join(",\n", sqlFields);
                    }
                    if (sqlPKeys.Count > 0)
                    {
                        sqlCreate += ",\n";
                        sqlCreate += $"PRIMARY KEY CLUSTERED ({string.Join(",", sqlPKeys)})";
                        sqlCreate += "\n";
                    }
                    else
                    {
                        sqlCreate += "\n";
                    }
                    sqlCreate += $")\n";
                    sqlCreate += $"GO\n";

                    sqlParagrap += sqlCreate;
                }

                // 创建索引语句
                if (sqlIndexs.Count > 0)
                {
                    foreach (var index in sqlIndexs) 
                    {
                        var sqlIdx = $"CREATE NONCLUSTERED INDEX [idx{index}] ";
                        sqlIdx += $"ON [dbo].[{table.TableName}] ([{index}])\n";
                        sqlIdx += $"GO\n";
                        sqlParagrap += sqlIdx;
                    }
                }

                // 添加注释语句
                if (sqlComments.Count > 0)
                {
                    sqlParagrap += string.Join("\nGO\n", sqlComments);
                }

                sqlParagraphs.Add(sqlParagrap);

                // 插入初始数据语句
                if (table.RowDatas.Count > 0)
                {
                    List<string> sqlInsertFields = table.DataFields.Select(x => $"[{x.FieldName}]").ToList();
                    List<string> sqlInsertValues = new List<string>();
                    foreach (var row in table.RowDatas)
                    {
                        List<string> values = new List<string>();
                        foreach (var field in table.DataFields)
                        {
                            var cell = row.CellDatas.FirstOrDefault(x => x.FieldName.Equals(field.FieldName, StringComparison.OrdinalIgnoreCase));
                            if (cell == null) continue;
                            switch (field.FieldType)
                            {
                                case FieldTypeEnum.String:
                                    values.Add($"'{cell.CellText}'");
                                    break;
                                case FieldTypeEnum.DateTime:
                                    {
                                        if (cell.CellText.Contains("getdate", StringComparison.OrdinalIgnoreCase))
                                        {
                                            values.Add($"{cell.CellText}");
                                        }
                                        else
                                        {
                                            values.Add($"'{cell.CellText}'");
                                        }
                                    }
                                    break;
                                case FieldTypeEnum.Int:
                                case FieldTypeEnum.Decimal:
                                case FieldTypeEnum.Float:
                                case FieldTypeEnum.Double:
                                case FieldTypeEnum.Long:
                                    values.Add($"{cell.CellText}");
                                    break;
                                default:
                                    values.Add($"'{cell.CellText}'");
                                    break;
                            }
                        }
                        sqlInsertValues.Add("(" + string.Join(",", values) + ")");
                    };
                    var sqlInsert = $"INSERT INTO [{table.TableName}]\n";
                    sqlInsert += "(" + string.Join(",", sqlInsertFields) + ")\n";
                    sqlInsert += "VALUES\n";
                    sqlInsert += string.Join(",\n", sqlInsertValues) + "\n";
                    sqlInsert += "GO\n";

                    sqlParagraphs.Add(sqlInsert);
                }

                //Table范围内的其他SQL语句
                if (table.ExtCodes.Count > 0)
                {
                    var sqlTableExtCode = string.Join("\n", table.ExtCodes) + "\nGO\n";
                    sqlParagraphs.Add(sqlTableExtCode);
                }
            }

            //全局其他SQL语句
            var sqlOther = string.Join("\n", readDocResult.ExtCodes) + "\nGO\n";
            sqlParagraphs.Add(sqlOther);

            return string.Join("\n\n", sqlParagraphs);
        }
    }
}
